package cn.codergege.demo.main

import cn.codergege.demo.util.DbUtil
import groovy.sql.Sql

import java.sql.ResultSet

/**
 * Created by codergege on 17-3-15.
 */
class DqQuery {
    /*
    义乌小商品城
    义乌全部
    金华地区非义乌,
    省内/分地区包括金华
    省外(大陆)
    非大陆(港澳台, 国外)
     */
    /**
     * 一区: 33078223
     * 二区: 33078225
     * 三区: 33078240
     * 四区: 33078234
     * 五区: 33078238
     * 篁园市场: 33078210
     * 生产资料市场: 33078252
     select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh
     where a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
     */
    void run() {
        Sql sql = DbUtil.sql

        String ywTotal = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and (b.sfzjhm like '330782%' or b.sfzjhm like '330725%')
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
"""
        String ywXspc = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and (b.sfzjhm like '330782%' or b.sfzjhm like '330725%')
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def jh = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3307%'
and b.sfzjhm not like '330782%' 
and b.sfzjhm not like '330725%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
"""
        def jhX = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3307%'
and b.sfzjhm not like '330782%' 
and b.sfzjhm not like '330725%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def hz = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3301%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
"""
        def hzX = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3301%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def nb = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3302%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
"""
        def nbX = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3302%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def wz = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3303%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
"""
        def wzX = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3303%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def jx = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3304%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
"""
        def jxX = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3304%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def huz = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3305%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
"""
        def huzX = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3305%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def sx = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3306%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
"""
        def sxX = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3306%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def qz = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3308%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
"""
        def qzX = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3308%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def zs = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3309%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
"""
        def zsX = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3309%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def tz = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3310%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
"""
        def tzX = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3310%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def ls = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3325%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
"""
        def lsX = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm like '3325%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        // 省外大陆
        def swdl = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm not like '33%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
"""
        def swdlX = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj like '801'
and b.sfzjhm not like '33%'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        // 非大陆
        def fdl = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj not like '801'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
"""
        def fdlX = """\
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.gj not like '801'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-31'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        println '-' * 40
        println '义乌地区'
        println '-' * 40
        println '全部:'
//        queryMethod sql, ywTotal
        println "小商品城: "
//        queryMethod sql, ywXspc

        println ''
        println '-' * 40
        println '金华(非义乌)'
        println '-' * 40
        println '全部:'
//        queryMethod(sql, jh)
        println "小商品城: "
//        queryMethod(sql, jhX)

        println ''
        println '-' * 40
        println '杭州'
        println '-' * 40
        println '全部:'
//        queryMethod(sql, hz)
        println "小商品城: "
//        queryMethod(sql, hzX)

        println ''
        println '-' * 40
        println '宁波'
        println '-' * 40
        println '全部:'
//        queryMethod(sql, nb)
        println "小商品城: "
//        queryMethod(sql, nbX)

        println ''
        println '-' * 40
        println '温州'
        println '-' * 40
        println '全部:'
//        queryMethod(sql, wz)
        println "小商品城: "
//        queryMethod(sql, wzX)

        println ''
        println '-' * 40
        println '嘉兴'
        println '-' * 40
        println '全部:'
//        queryMethod(sql, jx)
        println "小商品城: "
//        queryMethod(sql, jxX)

        println ''
        println '-' * 40
        println '湖州'
        println '-' * 40
        println '全部:'
//        queryMethod(sql, huz)
        println "小商品城: "
//        queryMethod(sql, huzX)

        println ''
        println '-' * 40
        println '绍兴'
        println '-' * 40
        println '全部:'
//        queryMethod(sql, sx)
        println "小商品城: "
//        queryMethod(sql, sxX)

        println ''
        println '-' * 40
        println '衢州'
        println '-' * 40
        println '全部:'
//        queryMethod(sql, qz)
        println "小商品城: "
//        queryMethod(sql, qzX)

        println ''
        println '-' * 40
        println '舟山'
        println '-' * 40
        println '全部:'
//        queryMethod(sql, zs)
        println "小商品城: "
//        queryMethod(sql, zsX)

        println ''
        println '-' * 40
        println '台州'
        println '-' * 40
        println '全部:'
//        queryMethod(sql, tz)
        println "小商品城: "
//        queryMethod(sql, tzX)

        println ''
        println '-' * 40
        println '丽水'
        println '-' * 40
        println '全部:'
//        queryMethod(sql, ls)
        println "小商品城: "
//        queryMethod(sql, lsX)

        println ''
        println '-' * 40
        println '省外(大陆)'
        println '-' * 40
        println '全部:'
//        queryMethod(sql, swdl)
        println "小商品城: "
//        queryMethod(sql, swdlX)

        println ''
        println '-' * 40
        println '非大陆(包含港澳台, 国外)'
        println '-' * 40
        println '全部:'
        queryMethod(sql, fdl)
        println "小商品城: "
        queryMethod(sql, fdlX)
    }

    static queryMethod(sql, querySql) {
        sql.query querySql, { ResultSet rs ->
            while (rs.next()) {
                println rs.getInt(1)
            }
        }
    }
}
